package dao.impl.CourseDaoImpl;

import common.model.Entity.courseEntity.CourseSchedule;
import common.util.DBUtil;
import dao.CourseDao.CourseScheduleDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * CourseScheduleDao 接口的实现类，用于操作课程安排表 t_course_schedule。
 * 提供增删改查、按课程ID查询、统计数量等功能。
 */
public class CourseScheduleDaoImpl implements CourseScheduleDao {

    private static final Logger logger = LoggerFactory.getLogger(CourseScheduleDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    /**
     * 根据ID查询课程安排。
     *
     * @param id 课程安排ID
     * @return 返回包含课程安排的 Optional，如果不存在返回 Optional.empty()
     */
    @Override
    public Optional<CourseSchedule> findById(Long id) {
        String sql = """
        SELECT
            id, course_id as courseId, day_of_week as dayOfWeek,
            start_time as startTime, end_time as endTime,
            location, created_at as createdAt, updated_at as updatedAt, is_deleted as isDeleted
        FROM t_course_schedule
        WHERE id = ? AND is_deleted = 0
        """;

        try (Connection conn = DBUtil.getConnection()) {
            CourseSchedule schedule = queryRunner.query(conn, sql,
                    new BeanHandler<>(CourseSchedule.class),
                    id
            );
            return Optional.ofNullable(schedule);
        } catch (SQLException e) {
            logger.error("根据ID查询课程安排失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有未删除的课程安排。
     *
     * @return 返回课程安排列表
     */
    @Override
    public List<CourseSchedule> findAll() {
        String sql = """
            SELECT
                id, course_id AS courseId, day_of_week AS dayOfWeek,
                start_time AS startTime, end_time AS endTime,
                location, created_at AS createdAt, updated_at AS updatedAt,
                is_deleted AS isDeleted
            FROM t_course_schedule
            WHERE is_deleted = 0
            ORDER BY course_id, day_of_week, start_time
            """;

        try {
            return queryRunner.query(DBUtil.getConnection(), sql,
                    new BeanListHandler<>(CourseSchedule.class));
        } catch (SQLException e) {
            logger.error("查询所有课程安排失败", e);
            return List.of();
        }
    }

    /**
     * 根据课程ID查询课程安排列表。
     *
     * @param courseId 课程ID
     * @return 返回该课程的所有课程安排列表
     */
    @Override
    public List<CourseSchedule> findByCourseId(Long courseId) {
        String sql = """
            SELECT
                id, course_id AS courseId, day_of_week AS dayOfWeek,
                start_time AS startTime, end_time AS endTime,
                location, created_at AS createdAt , updated_at AS updatedAt,
                is_deleted AS isDeleted
            FROM t_course_schedule
            WHERE course_id = ? AND is_deleted = 0
            ORDER BY day_of_week, start_time
            """;

        try {
            return queryRunner.query(DBUtil.getConnection(), sql,
                    new BeanListHandler<>(CourseSchedule.class), courseId);
        } catch (SQLException e) {
            logger.error("根据课程ID查询课程安排失败: courseId={}", courseId, e);
            return List.of();
        }
    }

    /**
     * 保存新的课程安排。
     *
     * @param schedule 课程安排实体
     * @return 保存成功返回 true，否则 false
     */
    @Override
    public boolean save(CourseSchedule schedule) {
        String sql = """
            INSERT INTO t_course_schedule
                (course_id, day_of_week, start_time, end_time, location, created_at)
            VALUES (?, ?, ?, ?, ?, ?)
            """;

        try {
            int affectedRows = queryRunner.update(DBUtil.getConnection(), sql,
                    schedule.getCourseId(),
                    schedule.getDayOfWeek(),
                    schedule.getStartTime(),
                    schedule.getEndTime(),
                    schedule.getLocation(),
                    LocalDateTime.now()
            );

            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("保存课程安排失败: {}", schedule, e);
            return false;
        }
    }

    /**
     * 更新课程安排信息。
     *
     * @param schedule 课程安排实体
     * @return 更新成功返回 true，否则 false
     */
    @Override
    public boolean update(CourseSchedule schedule) {
        String sql = """
            UPDATE t_course_schedule SET
                course_id = ?, day_of_week = ?, start_time = ?,
                end_time = ?, location = ?, updated_at = ?
            WHERE id = ? AND is_deleted = 0
            """;

        try {
            int affectedRows = queryRunner.update(DBUtil.getConnection(), sql,
                    schedule.getCourseId(),
                    schedule.getDayOfWeek(),
                    schedule.getStartTime(),
                    schedule.getEndTime(),
                    schedule.getLocation(),
                    LocalDateTime.now(),
                    schedule.getId()
            );

            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("更新课程安排失败: {}", schedule, e);
            return false;
        }
    }

    /**
     * 根据ID逻辑删除课程安排。
     *
     * @param id 课程安排ID
     * @return 删除成功返回 true，否则 false
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_course_schedule SET is_deleted = 1, updated_at = ? WHERE id = ?";

        try {
            int affectedRows = queryRunner.update(DBUtil.getConnection(), sql,
                    LocalDateTime.now(), id);
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("删除课程安排失败: id={}", id, e);
            return false;
        }
    }

    /**
     * 根据课程ID逻辑删除该课程的所有安排。
     *
     * @param courseId 课程ID
     * @return 删除成功返回 true，否则 false
     */
    public boolean deleteByCourseId(Long courseId) {
        String sql = "UPDATE t_course_schedule SET is_deleted = 1, updated_at = ? WHERE course_id = ?";

        try {
            int affectedRows = queryRunner.update(DBUtil.getConnection(), sql,
                    LocalDateTime.now(), courseId);
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("根据课程ID删除课程安排失败: courseId={}", courseId, e);
            return false;
        }
    }

    /**
     * 统计未删除课程安排数量。
     *
     * @return 返回未删除课程安排数量
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_course_schedule WHERE is_deleted = 0";

        try {
            Long count = queryRunner.query(DBUtil.getConnection(), sql, new ScalarHandler<>());
            return count != null ? count : 0;
        } catch (SQLException e) {
            logger.error("统计课程安排数量失败", e);
            return 0;
        }
    }
}
